package com.autoEcole.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.autoEcole.exception.DAOException;
import com.autoEcole.model.DatePermis;
import com.autoEcole.model.Eleve;
import com.autoEcole.model.Etat;
import com.autoEcole.model.Permis;
import com.autoEcole.util.ConnectionUtil;
import com.autoEcole.util.DateUtil;

public class DatePermisDAO {
	/**
	 * LOGGER
	 */
	private static final Logger LOGGER = Logger.getLogger(DatePermisDAO.class);

	/**
	 * Query Insert
	 */
	private static final String CREATE_QUERY = "INSERT INTO date_permis (ELEVE_ID, PERMIS_ID, DATE_DEBUT, DATE_FIN, SOMME_TOTAL, SOMME_PAYE, ETAT_ID) VALUES (?,?,?,?,?,?,?)";

	/**
	 * Query Update
	 */
	private static final String UPDATE_QUERY = "UPDATE date_permis SET DATE_DEBUT = ?, DATE_FIN = ?, SOMME_TOTAL = ?, SOMME_PAYE = ?, ETAT_ID = ? WHERE ELEVE_ID = ? AND PERMIS_ID = ?";

	/**
	 * Query Find By CRITERE
	 */
	private static final String FIND_BY_ELEVE_ID_QUERY = "SELECT ELEVE_ID, PERMIS_ID, DATE_DEBUT, DATE_FIN, SOMME_TOTAL, SOMME_PAYE, ETAT_ID FROM date_permis WHERE ELEVE_ID = ?";
	
	/**
	 * Query Delete
	 */
	private static final String DELETE_QUERY = "DELETE FROM date_permis WHERE ELEVE_ID = ? AND PERMIS_ID = ?";

	/**
	 * DatePermisDAO
	 */
	private static DatePermisDAO datePermisDAO;
    
    /**
	 * Constructor
	 */
	private DatePermisDAO() {
		super();
	}

	/**
	 * Returns the DatePermisDAO instance (Design Pattern : Singleton)
	 * 
	 * @return DatePermisDAO
	 */
	public static synchronized DatePermisDAO getInstance() {
		if (datePermisDAO == null)
			datePermisDAO = new DatePermisDAO();
		return datePermisDAO;
	}
	
	/**
	 * 
	 */
	public void create(DatePermis datePermis) throws DAOException {
		LOGGER.debug("Begin DatePermisDAO#create(datePermis datePermis)");
		if (datePermis == null)
			throw new DAOException("DatePermisDAO#create(datePermis datePermis) doesn't accept parameter with null value ");
		PreparedStatement prepStatement = null;
		try {
			Connection connection = ConnectionUtil.getConnection();
			LOGGER.debug("Query to execute : " + CREATE_QUERY);
			prepStatement = connection.prepareStatement(CREATE_QUERY);
			prepStatement.setInt(1, datePermis.getEleve().getEleveId());
			prepStatement.setInt(2, datePermis.getPermis().getPermisId());
			prepStatement.setDate(3, DateUtil.getInstance().date2SqlDate(datePermis.getDateDebut()));
			prepStatement.setDate(4, DateUtil.getInstance().date2SqlDate(datePermis.getDateFin()));
			prepStatement.setInt(5, datePermis.getSommeTotal());
			prepStatement.setInt(6, datePermis.getSommePaye());
			prepStatement.setInt(7, datePermis.getEtat().getEtatId());
			prepStatement.executeUpdate();
			LOGGER.debug("End DatePermisDAO#create(datePermis datePermis)");
		} catch (SQLException ex) {
			LOGGER.error(ex.getMessage());
			throw new DAOException(ex);
		} finally {
			ConnectionUtil.closePreparedStatement(prepStatement);
		}
	}

	/**
	 * 
	 */
	public void update(DatePermis datePermis) throws DAOException {
		LOGGER.debug("Begin DatePermisDAO#update(datePermis datePermis)");
		if (datePermis == null)
			throw new DAOException("DatePermisDAO#update(datePermis datePermis) doesn't accept parameter with null value ");
		PreparedStatement prepStatement = null;
		try {
			Connection connection = ConnectionUtil.getConnection();
			LOGGER.debug("Query to execute : " + UPDATE_QUERY);
			prepStatement = connection.prepareStatement(UPDATE_QUERY);
			prepStatement.setDate(1, DateUtil.getInstance().date2SqlDate(datePermis.getDateDebut()));
			prepStatement.setDate(2, DateUtil.getInstance().date2SqlDate(datePermis.getDateFin()));
			prepStatement.setInt(3, datePermis.getSommeTotal());
			prepStatement.setInt(4, datePermis.getSommePaye());
			prepStatement.setInt(5, datePermis.getEtat().getEtatId());
			prepStatement.setInt(6, datePermis.getEleve().getEleveId());
			prepStatement.setInt(7, datePermis.getPermis().getPermisId());
			prepStatement.executeUpdate();
			LOGGER.debug("End DatePermisDAO#update(datePermis datePermis)");
		} catch (SQLException ex) {
			LOGGER.error(ex.getMessage());
			throw new DAOException(ex);
		} finally {
			ConnectionUtil.closePreparedStatement(prepStatement);
		}
	}

	/**
	 * 
	 */
	public List<DatePermis> findByEleveId(int eleveId) throws DAOException {
		LOGGER.debug("Begin DatePermisDAO#findById(int eleveId)");
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;
		try {
			Connection connection = ConnectionUtil.getConnection();
			LOGGER.debug("Query to execute : " + FIND_BY_ELEVE_ID_QUERY);
			preparedStatement = connection.prepareStatement(FIND_BY_ELEVE_ID_QUERY);
			preparedStatement.setInt(1, eleveId);
			resultSet = preparedStatement.executeQuery();
            List<DatePermis> resultList = getDatePermisList(resultSet);
			LOGGER.debug("End DatePermisDAO#findById(int ideleveId)");
			return resultList;
		} catch (SQLException ex) {
			LOGGER.error(ex.getMessage());
			throw new DAOException(ex);
		} finally {
			ConnectionUtil.closeResutlSet(resultSet);
			ConnectionUtil.closePreparedStatement(preparedStatement);
		}
	}
	
	public void delete(DatePermis datePermis) throws DAOException {
		LOGGER.debug("Begin DatePermisDAO#delete(int datePermisId)");
		PreparedStatement prepStatement = null;
		try {
			Connection connection = ConnectionUtil.getConnection();
			LOGGER.debug("Query to execute : " + DELETE_QUERY);
			prepStatement = connection.prepareStatement(DELETE_QUERY);
			prepStatement.setInt(1, datePermis.getEleve().getEleveId());
			prepStatement.setInt(2, datePermis.getPermis().getPermisId());
			prepStatement.executeUpdate();
			prepStatement.close();
			LOGGER.debug("End DatePermisDAO#delete(int datePermisId)");
		} catch (Exception e) {
			throw new DAOException(e);
		} finally {
			ConnectionUtil.closePreparedStatement(prepStatement);
		}
	}

	/** On charge permis et etat
     * @param resultSet
     * @return List<datePermis>
     * @throws SQLException
     */
    private List<DatePermis> getDatePermisList(ResultSet resultSet) throws SQLException, DAOException {
        List<DatePermis> resultList = new ArrayList<DatePermis>();
        while (resultSet.next()) {
            DatePermis datePermis = new DatePermis();
            datePermis.setDateDebut(resultSet.getDate("DATE_DEBUT"));
            datePermis.setDateFin(resultSet.getDate("DATE_FIN"));
            datePermis.setSommeTotal(resultSet.getInt("SOMME_TOTAL"));
            datePermis.setSommePaye(resultSet.getInt("SOMME_PAYE"));
            Permis permis = new Permis();
            permis.setPermisId(resultSet.getInt("PERMIS_ID"));
            datePermis.setPermis(permis);
            Etat etat = new Etat();
            etat.setEtatId(resultSet.getInt("ETAT_ID"));
            datePermis.setEtat(etat);
            Eleve eleve = new Eleve();
            eleve.setEleveId(resultSet.getInt("ELEVE_ID"));
            datePermis.setEleve(eleve);            
            resultList.add(datePermis);
        }
        return resultList;
    }    
}
